"""
图书搜索查询类
使用原生SQL优化查询性能，专门处理图书搜索相关的复杂查询
"""

import aiomysql
from typing import List, Dict, Any, Optional
from db import DatabaseConnection, db_connection


class BookSearchQuery:
    """图书搜索查询类，使用原生SQL优化性能"""

    def __init__(self):
        self.db_connection: DatabaseConnection = db_connection

    async def search_books_by_keyword(
        self,
        keyword: str,
        page: int = 1,
        limit: int = 10,
        search_fields: List[str] = None,
        order_by: str = "rating DESC, reading_count DESC",
    ) -> tuple[int, List[Dict[str, Any]]]:
        """
        使用关键字搜索图书，支持多字段模糊搜索

        Args:
            keyword: 搜索关键字
            page: 页码，从1开始
            limit: 每页数量
            search_fields: 搜索字段列表，默认为['title', 'author', 'intro']
            order_by: 排序方式，默认按评分和阅读量降序

        Returns:
            tuple[int, List[Dict[str, Any]]]: 返回总数和图书列表
        """
        if not keyword.strip():
            return 0, []

        if search_fields is None:
            search_fields = ["title", "author", "intro"]

        offset = (page - 1) * limit
        search_keyword = f"%{keyword.strip()}%"

        # 构建搜索条件
        search_conditions = []
        search_params = []

        for field in search_fields:
            if field in ["title", "author", "translator", "intro", "language"]:
                search_conditions.append(f"b.{field} LIKE %s")
                search_params.append(search_keyword)

        where_clause = " OR ".join(search_conditions) if search_conditions else "1=0"

        # 查询图书详细信息的SQL
        sql = f"""
        SELECT 
            b.*,
            GROUP_CONCAT(DISTINCT mc.main_category) as main_categories,
            GROUP_CONCAT(DISTINCT sc.sub_category) as sub_categories,
            CASE 
                WHEN b.title LIKE %s THEN 3
                WHEN b.author LIKE %s THEN 2
                ELSE 1
            END as relevance_score
        FROM books b
        LEFT JOIN book_main_categories mc ON b.book_id = mc.book_id
        LEFT JOIN book_sub_categories sc ON b.book_id = sc.book_id
        WHERE ({where_clause})
        GROUP BY b.book_id
        ORDER BY relevance_score DESC, {order_by}
        LIMIT %s OFFSET %s
        """

        # 查询总数的SQL
        count_sql = f"""
        SELECT COUNT(DISTINCT b.book_id) as total
        FROM books b
        WHERE ({where_clause})
        """

        pool = await self.db_connection.get_pool()

        try:
            async with pool.acquire() as conn:
                async with conn.cursor(aiomysql.DictCursor) as cursor:
                    # 构建完整的参数列表
                    # 为relevance_score准备的参数
                    relevance_params = [search_keyword, search_keyword]
                    # 完整的查询参数
                    full_params = relevance_params + search_params + [limit, offset]

                    # 先查询总数
                    await cursor.execute(count_sql, search_params)
                    count_result = await cursor.fetchone()
                    total = count_result["total"] if count_result else 0

                    # 查询图书数据
                    await cursor.execute(sql, full_params)
                    books = await cursor.fetchall()

                    # 处理分类数据
                    for book in books:
                        if book.get("main_categories"):
                            book["main_categories"] = book["main_categories"].split(",")
                        else:
                            book["main_categories"] = []

                        if book.get("sub_categories"):
                            book["sub_categories"] = book["sub_categories"].split(",")
                        else:
                            book["sub_categories"] = []

                        # 移除relevance_score字段（内部使用）
                        book.pop("relevance_score", None)

                    return total, books if books else []

        except Exception as e:
            print(f"搜索图书失败: {e}")
            return 0, []
